import mysql.connector

class DataBase:
    host = 'localhost'
    user = 'root'
    passwd = 'root'
    database = 'runoob_db'
    db = ''
    cursor = ''

    # 初始化数据库
    def __init__(self, host='localhost', user='root', passwd='root', database='runoob_db'):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.database = database
        self.get_db()

    # 连接数据库
    # mysql8报错：auth_plugin='caching_sha2_password'
    # 执行命令：pip install MySQL-connector-python
    def get_db(self):
        try:
            self.db = mysql.connector.connect(
                host=self.host,
                user=self.user,
                passwd=self.passwd,
                database=self.database
            )
            self.get_cursor()
        except Exception as e:
            print("连接数据库：" + e)
        return self.db

    # 获取数据库游标
    def get_cursor(self):
        self.cursor = self.db.cursor()
        return self.cursor

    # 执行没有事务的语句
    def execute(self, sql):
        try:
            self.execute(sql)
        except Exception as e:
            print("执行sql语句错误：" + e)

    # 批量插入 ，返回成功插入记录数
    def insert_batch(self, sql, values):
        self.cursor.executemany(sql, values)
        self.db.commit()
        return self.cursor.rowcount

    # 增加/删除/修改，返回成功条数
    def insert_or_delete_or_update(self, sql):
        self.cursor.execute(sql)
        self.db.commit()
        return self.cursor.rowcount

    # 查询单条
    def select_one(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchone()

    # 查询多条
    def select_all(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()


db = DataBase()
version = db.select_one("SELECT VERSION(), SYSDATE()")
print(version)
# print("Database version is :%s" %version)
# 查询所有的employee
employee = db.select_all("SELECT * FROM EMPLOYEE")
print(employee)
for row in employee:
    fname = row[0]
    lname = row[1]
    age = row[2]
    sex = row[3]
    income = row[4]
    # 打印结果
    print("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
          (fname, lname, age, sex, income))
# 创建数据库
